using System;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.Collections.Generic;
using System.Globalization;
using System.Diagnostics.CodeAnalysis;
using System.Diagnostics.Contracts;

namespace Granite.Data
{

	/// <summary>
	/// This represents all of the connection information needed to make a procedure call to a SQL Server database.
	/// </summary>
	/// <remarks>This class is thread-safe</remarks>
	public class SqlServerConnection : Connection
	{
		readonly string m_ConnectionString;

		readonly TimeSpan? m_DefaultTimeout;

		/// <summary>
		/// This represents all of the connection information needed to make a procedure call to a SQL Server database.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="defaultTimeout"></param>
		public SqlServerConnection(string connectionString, TimeSpan? defaultTimeout = null)
		{
			if (connectionString == null)
				throw new ArgumentNullException("connectionString", "connectionString is null or empty.");
			if (String.IsNullOrEmpty(connectionString))
				throw new ArgumentException("connectionString is empty.", "connectionString");
			Contract.EndContractBlock();

			m_ConnectionString = connectionString;
			m_DefaultTimeout = defaultTimeout;
		}

		internal string ConnectionString
		{
			get { return m_ConnectionString; }
		}

		/// <summary>
		/// Default timeout for procedure calls. May be overriden by a specific procedure.
		/// </summary>
		public TimeSpan? DefaultTimeout
		{
			get { return m_DefaultTimeout; }
		}

		/// <summary>
		/// Returns a DataRow instead of a lightweight row. Used mainly for legacy code.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="locale"></param>
		/// <param name="options">Controls what happens when the wrong number of rows are returned</param>
		/// <returns></returns>
		public DataRow ExecuteDataRow(ProcedureCall procedure, CultureInfo locale, ExecuteRowOptions options)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			var dt = ExecuteDataTable(procedure, locale);
			if (dt.Rows.Count == 0)
			{
				if (options.HasFlag(ExecuteRowOptions.AllowEmptyResults))
					return null;
				else
				{
					var ex = new DataException("No rows were returned");
					ex.Data["ConnectionString"] = m_ConnectionString;
					ex.Data["ProcedureCall"] = procedure.ToString();
					throw ex;
				}
			}
			else
				if (dt.Rows.Count > 1 && !options.HasFlag(ExecuteRowOptions.DiscardExtraRows))
				{
					var ex = new DataException("Expected 1 row but received " + dt.Rows.Count + " rows");
					ex.Data["ConnectionString"] = m_ConnectionString;
					ex.Data["ProcedureCall"] = procedure.ToString();
					ex.Data["RowCount"] = dt.Rows.Count;
					throw ex;
				}
			return dt.Rows[0];
		}

		/// <summary>
		/// Returns a DataRow instead of a lightweight row. Used mainly for legacy code.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="locale"></param>
		/// <returns></returns>
		public DataRow ExecuteDataRow(ProcedureCall procedure, CultureInfo locale)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			return ExecuteDataRow(procedure, locale, ExecuteRowOptions.None);
		}

		/// <summary>
		/// Returns a DataSet using the indicated table names.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="locale"></param>
		/// <param name="tables">Names for the tables that are returned.</param>
		/// <returns></returns>
		public DataSet ExecuteDataSet(ProcedureCall procedure, CultureInfo locale, params string[] tables)
		{
			//TODO: Create a version of this that returns a Dictionary of Table.

			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			var ds = new DataSet();
			ds.Locale = locale ?? CultureInfo.InvariantCulture;
			ExecuteCore(procedure, cmd => ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, tables));
			return ds;
		}

		/// <summary>
		/// Returns a DataTable instead of a lightweight Table. Used mainly for legacy code and binding to WebForms grids.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="locale"></param>
		/// <returns></returns>
		public DataTable ExecuteDataTable(ProcedureCall procedure, CultureInfo locale)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			var dt = new DataTable();
			dt.Locale = locale ?? CultureInfo.InvariantCulture;
			ExecuteCore(procedure, cmd => dt.Load(cmd.ExecuteReader()));
			return dt;
		}

		/// <summary>
		/// Executes the procedure, discarding any results
		/// </summary>
		/// <param name="procedure"></param>
		/// <returns></returns>
		public override void ExecuteNonQuery(ProcedureCall procedure)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			ExecuteCore(procedure, cmd => cmd.ExecuteNonQuery());
		}

		/// <summary>
		/// Executes the procedure, returning a single row.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="options">Controls what happens if the wrong number of rows is returned</param>
		/// <returns></returns>
		public override IDictionary<string, object> ExecuteRow(ProcedureCall procedure, ExecuteRowOptions options)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			var table = ExecuteTable(procedure);

			if (table.Count == 0)
			{
				if (options.HasFlag(ExecuteRowOptions.AllowEmptyResults))
					return null;
				else
				{
					var ex = new DataException("No rows were returned");
					ex.Data["ConnectionString"] = m_ConnectionString;
					ex.Data["ProcedureCall"] = procedure.ToString();
					throw ex;
				}
			}
			else
				if (table.Count > 1 && !options.HasFlag(ExecuteRowOptions.DiscardExtraRows))
				{
					var ex = new DataException("Expected 1 row but received " + table.Count + " rows");
					ex.Data["ConnectionString"] = m_ConnectionString;
					ex.Data["ProcedureCall"] = procedure.ToString();
					ex.Data["RowCount"] = table.Count;
					throw ex;
				}
			return table[0];
		}

		/// <summary>
		/// Executes the procedure, returning a single nullable value.
		/// </summary>
		/// <param name="procedure"></param>
		/// <returns></returns>
		public override T? ExecuteScalar<T>(ProcedureCall procedure)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			object temp = null;
			ExecuteCore(procedure, cmd => temp = cmd.ExecuteScalar());
			if (temp == DBNull.Value || temp == null)
				return null;

			return (T)temp;
		}

		/// <summary>
		/// Executes the procedure, returning a single nullable value.
		/// </summary>
		/// <param name="procedure"></param>
		/// <returns></returns>
		public override string ExecuteString(ProcedureCall procedure)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			object temp = null;
			ExecuteCore(procedure, cmd => temp = cmd.ExecuteScalar());
			if (temp == DBNull.Value)
				return null;

			return (string)temp;
		}

		/// <summary>
		/// Executes the procedure, returning a single Table.
		/// </summary>
		/// <param name="procedure"></param>
		/// <returns></returns>
		public override Table ExecuteTable(ProcedureCall procedure)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			Table t = null;
			ExecuteCore(procedure, cmd =>
			{
				using (var reader = cmd.ExecuteReader())
				{
					t = new Table(reader);
				}
			});

			return t;
		}

		/// <summary>
		/// Executes the procedure, returning a single Table.
		/// </summary>
		/// <param name="procedure"></param>
		/// <returns></returns>
		public override IList<Table> ExecuteTables(ProcedureCall procedure)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			Contract.EndContractBlock();

			List<Table> tables = new List<Table>();

			ExecuteCore(procedure, cmd =>
			{
				using (var reader = cmd.ExecuteReader())
				{
					do
						tables.Add(new Table(reader));
					while (reader.NextResult());
				}
			});

			return tables;
		}

		/// <summary>
		/// Extension point for overriding how procedures are executed.
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="action">This receives a SqlCommand that has been executed.</param>
		protected virtual void ExecuteCore(ProcedureCall procedure, Action<SqlCommand> action)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			if (action == null)
				throw new ArgumentNullException("action", "action is null.");
			Contract.EndContractBlock();

			var startTime = DateTimeOffset.Now;
			OnProcedureCallStarted(procedure, startTime);
			try
			{
				using (var con = CreateSqlConnection())
				{
					using (var cmd = CreateStoredProcedure(procedure, con))
					{
						action(cmd);
					}
				}
				OnProcedureCallFinished(procedure, startTime, DateTimeOffset.Now);
			}
			catch (SqlException ex)
			{
				ex.Data["ConnectionString"] = m_ConnectionString;
				ex.Data["ProcedureCall"] = procedure.ToString();
				OnProcedureCallError(procedure, startTime, DateTimeOffset.Now, ex);
				throw;
			}
			catch (SqlTypeException ex)
			{
				ex.Data["ConnectionString"] = m_ConnectionString;
				ex.Data["ProcedureCall"] = procedure.ToString();
				OnProcedureCallError(procedure, startTime, DateTimeOffset.Now, ex);
				throw;
			}
		}

		[SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
		internal SqlConnection CreateSqlConnection()
		{
			var con = new SqlConnection(m_ConnectionString);
			con.Open();
			using (var cmd = new SqlCommand("set arithabort on", con))
			{
				cmd.ExecuteNonQuery();
			}
			using (var cmd = new SqlCommand("set XACT_ABORT on", con))
			{
				cmd.ExecuteNonQuery();
			}
			return con;
		}

		/// <summary>
		/// Creates a new stored procedure based on the supplied ProcedureCall
		/// </summary>
		/// <param name="procedure"></param>
		/// <param name="connection"></param>
		/// <returns></returns>
		[SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
		[SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
		protected SqlCommand CreateStoredProcedure(ProcedureCall procedure, SqlConnection connection)
		{
			if (procedure == null)
				throw new ArgumentNullException("procedure", "procedure is null.");
			if (connection == null)
				throw new ArgumentNullException("connection", "connection is null.");
			Contract.EndContractBlock();

			var cmd = new SqlCommand();
			cmd.Connection = connection;
			cmd.CommandText = procedure.ProcedureName;
			cmd.CommandType = CommandType.StoredProcedure;
			if (procedure.Timeout.HasValue)
				cmd.CommandTimeout = (int)procedure.Timeout.Value.TotalSeconds;
			else if (DefaultTimeout.HasValue)
				cmd.CommandTimeout = (int)DefaultTimeout.Value.TotalSeconds;

			foreach (var item in procedure.Parameters)
			{
				if (item.Value == null)
					cmd.Parameters.AddWithValue("@" + item.Key, DBNull.Value);
				else
					cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
			}

			return cmd;
		}

        /// <summary>
        /// Creates a new transaction
        /// </summary>
        /// <param name="forwardEvents">If true, logging events are forwarded to the parent connection. </param>
        /// <returns></returns>
        public override ITransactionalConnection BeginTransaction(bool forwardEvents = true)
        {
            return new TransactionalSqlServerConnection(this, forwardEvents);
        }

		/// <summary>
		/// Indicates if transaction support is available
		/// </summary>
		public override bool CanBeginTransaction
		{
			get { return true; }
		}

	}
}

